package edu.utdallas_elearn_calendar.utdallas_elearn_calendar_db;

import edu.utdallas_elearn_calendar.utdallas_elearn_calendar_db.CalDatabaseHelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class CalDbAdapter {
	// Class vars
	private Context context;
	private SQLiteDatabase database;
	private CalDatabaseHelper dbHelper;
	
	// Shared fields - Primary Key
	public static final String ROW_ID = "_id";
	
	// Shared fields - Foreign Key
	public static final String CLASS_ID = "class_id";
	
	// Class table fields
	public static final String CLASS_PREFIX = "prefix";
	public static final String CLASS_NUMBER = "class_number";
	public static final String CLASS_SECTION = "class_section";
	public static final String CLASS_TITLE = "title";
	private static final String CLASS_TABLE = "univ_classes";
	
	// View of combined class table with class detail table
	private static final String CLASS_VIEW = "v_univ_classes";
	
	// View of combined events table with event type, event priority, and event reminders
	private static final String EVENTS_VIEW = "v_events";
	
	// Class Details table fields
	public static final String DETAIL_ID = "detail_id";
	public static final String CLASS_SEMESTER = "semester";
	public static final String CLASS_PROFESSOR = "professor";
	public static final String CLASS_TIME = "time";	
	public static final String CLASS_LOCATION = "location";
	private static final String CLASS_DETAIL_TABLE = "univ_class_detail";		
	
	// Events table fields
	//public static final String CLASS_ID = "class_id";
	public static final String EVENT_ID = "event_id";
	public static final String EVENT_TYPE_ID = "event_type_id";
	public static final String EVENT_NAME = "name";
	public static final String EVENT_DUE = "due_date";
	public static final String EVENT_RECURRENCE = "recurring_type";  // 1-daily, 2-weekly, 3-monthly, 4-yearly
	private static final String EVENT_TABLE = "events";
	
	// Event Reminder table fields
	public static final String REMINDER_ID = "reminder_id";
	public static final String REMINDER_EVENT_ID = "event_id";
	public static final String REMINDER_MINUTES = "minutes_prior";
	private static final String REMINDER_TABLE = "event_remind";	
	
	// Event types table fields
	public static final String EVENT_PRIORITY = "priority_id";
	public static final String EVENT_TYPE = "event_type";
	private static final String EVENT_TYPE_TABLE = "event_types";
	
	// Priority level table fields
	public static final String PRIORITY_VAL = "priority_val";
	public static final String PRIORITY_COLOR = "color";
	private static final String PRIORITY_TABLE = "priority_level";
	
	// Elearning log table fields
	public static final String ELEARN_LOG_DATE = "log_date";
	public static final String ELEARN_LOG_STATUS = "log_status";
	private static final String ELEARN_LOG_TABLE = "elearn_log";
	
	
	// CalDbAdapter Constructor
	public CalDbAdapter(Context context) {
		this.context = context;
	}

	// open the database to write into it
	public CalDbAdapter open() throws SQLException {
		dbHelper = new CalDatabaseHelper(context);
		database = dbHelper.getWritableDatabase();

		// Initialize the db if this is a fresh install
		Cursor result = getAllPriorities();
		
		if(result.getCount() == 0)
		{
			initialize();
		}
		
		result.close();
		
		return this;
	}
	
	// reset the database
	public void reset() {
		dbHelper.onUpgrade(database, 1, 1);
		initialize();		
	}
	
	private void initialize() {
		// setup look up tables
		insertPriority(1, "#FF0000");
		insertPriority(2, "#0000FF");
		insertPriority(3, "#007500");
		insertPriority(4, "#000000");		
		
		insertEventType(4, "Miscellaneous");
		insertEventType(1, "Test");
		insertEventType(2, "Quiz");
		insertEventType(3, "Assignment");
		
		insertEmptyClass();
	}	
	
	// close the database
	public void close() {
		dbHelper.close();
	}
	
	/********************** Methods dealing with Classes *********************/
	
	/**
	 * Create content values for the class table 
	 * @param prefix
	 * @param class_number
	 * @param class_section
	 * @param title
	 * @return ContentValues
	 */
	private ContentValues createClassValues(String prefix, String class_number, 
			String class_section, String title) {
		
		ContentValues values = new ContentValues();
		values.put(CLASS_PREFIX, prefix);
		values.put(CLASS_NUMBER, class_number);
		values.put(CLASS_SECTION, class_section);
		values.put(CLASS_TITLE, title);
		return values;
	}
	
	/**
	 * Insert a new class into the database
	 * If the class is successfully created then return the new
	 * rowId for that class, otherwise return a -1 to indicate failure.
	 * @param prefix
	 * @param class_number
	 * @param class_section
	 * @param title
	 * @return
	 */
	private void insertEmptyClass() {
		String sql = "INSERT INTO " + CLASS_TABLE + 
				" (_id, prefix, class_number, class_section, title) VALUES(0, '', '', '', 'MISC')";
		
		database.execSQL(sql);
	}	
	
	/**
	 * Insert a new class into the database
	 * If the class is successfully created then return the new
	 * rowId for that class, otherwise return a -1 to indicate failure.
	 * @param prefix
	 * @param class_number
	 * @param class_section
	 * @param title
	 * @return
	 */
	public long insertClass(String prefix, String class_number,
			String class_section, String title) {
		
		if (prefix.length() > 0 && class_number.length() > 0 &&
				class_section.length() > 0) {
			Cursor mCursor = getClass(prefix, class_number, class_section);
					
			if (mCursor.getCount() > 0) {
				updateClass(mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID)),
						prefix, class_number, class_section, title);
				return mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID));
			}
			
			ContentValues classValues = createClassValues(prefix, class_number, class_section, title);
	
			return database.insert(CLASS_TABLE, null, classValues);
		}
		else {
			return -1;
		}
	}
	
	/**
	 * Update a class
	 * @param rowId
	 * @param prefix
	 * @param class_number
	 * @param class_section
	 * @param title
	 * @return
	 */
	public boolean updateClass(long rowId, String prefix, String class_number,
			String class_section, String title) {
		
		if (rowId != 0) {
			ContentValues updateValues = createClassValues(prefix, class_number, class_section, title);
			
			return database.update(CLASS_TABLE, updateValues, ROW_ID + "="
					+ rowId, null) > 0;
		}
		else {
			return false;
		}
	}

	/**
	 * Delete a class
	 * @param rowId
	 * @return
	 */
	public boolean deleteClass(long rowId) {
		// first delete all associated events
		deleteClassEvents(rowId);
		
		// now delete the class details
		deleteClassDetail(rowId);
		
		return database.delete(CLASS_TABLE, ROW_ID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Get all of the classes from the class view
	 * includes the class and details
	 * @return Cursor
	 */
	public Cursor getViewAllClasses() {
		return database.query(CLASS_VIEW, new String[] { CLASS_ID,
				CLASS_PREFIX, CLASS_NUMBER, CLASS_SECTION,
				CLASS_TITLE, DETAIL_ID, CLASS_SEMESTER,
				CLASS_PROFESSOR, CLASS_TIME, CLASS_LOCATION }, null, null, null, null, null);		
	}
	
	/**
	 * Get all of the classes
	 * @return Cursor
	 */
	public Cursor getAllClasses() {
		return database.query(CLASS_TABLE, new String[] { ROW_ID,
				CLASS_PREFIX, CLASS_NUMBER, CLASS_SECTION,
				CLASS_TITLE }, null, null, null, null, null);		
	}
	
	/**
	 * Get a cursor positioned at a particular class
	 * from the view
	 * @param rowId
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getViewClass(long rowId) throws SQLException {
		Cursor mCursor = database.query(CLASS_VIEW, new String[] {
				CLASS_ID, CLASS_PREFIX, CLASS_NUMBER, CLASS_SECTION, 
				CLASS_TITLE, DETAIL_ID, CLASS_SEMESTER,
				CLASS_PROFESSOR, CLASS_TIME, CLASS_LOCATION }, 
				CLASS_ID + "=" + rowId, 
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}	
	
	/**
	 * Get a cursor positioned at a particular class
	 * @param rowId
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getClass(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, CLASS_TABLE, new String[] {
				ROW_ID, CLASS_PREFIX, CLASS_NUMBER, CLASS_SECTION, 
				CLASS_TITLE }, ROW_ID + "=" + rowId, 
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}	
	
	/**
	 * Get a cursor positioned at a particular class
	 * @param rowId
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getClass(String prefix, String class_number,
			String class_section) throws SQLException {
		Cursor mCursor = database.query(true, CLASS_TABLE, new String[] {
				ROW_ID, CLASS_PREFIX, CLASS_NUMBER, CLASS_SECTION, 
				CLASS_TITLE }, CLASS_PREFIX + "=? AND " +
				CLASS_NUMBER + "=? AND " + CLASS_SECTION + "=?", 
				new String[]{prefix, class_number, class_section},
				null, null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}	

	/********************** Methods dealing with Class Details *********************/
	
	/**
	 * Create content values for the class detail table 
	 * @param class_id
	 * @param semester
	 * @param professor
	 * @param time
	 * @param location
	 * @return ContentValues
	 */
	private ContentValues createClassDetailValues(long class_id, String semester, 
			String professor, String time, String location) {
		
		ContentValues values = new ContentValues();
		values.put(CLASS_ID, class_id);
		values.put(CLASS_SEMESTER, semester);
		values.put(CLASS_PROFESSOR, professor);
		values.put(CLASS_TIME, time);
		values.put(CLASS_LOCATION, location);
		return values;
	}
		
	
	/**
	 * Insert the class details into the database
	 * If the class details is successfully created then return the new
	 * rowId for that class, otherwise return a -1 to indicate failure.
	 * @param class_id
	 * @param semester
	 * @param professor
	 * @param time
	 * @param location
	 * @return
	 */
	public long insertClassDetail(long class_id, String semester,
			String professor, String time, String location) {
		
		if (semester.length() > 0 || professor.length() > 0 ||
				time.length() > 0 || location.length() > 0) {
			if (class_id != 0) {
				Cursor mCursor = getClassDetail(class_id);
				
				if (mCursor.getCount() > 0) {
					updateClassDetail(class_id, semester, professor, time, location);
					return mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID));
				}
				
				ContentValues classDetailValues = createClassDetailValues(class_id, semester, professor, time, location);
		
				return database.insert(CLASS_DETAIL_TABLE, null, classDetailValues);
			}
			else {
				return -1;
			}
		}
		else {
			return -1;
		}
	}
	
	/**
	 * Update the class details
	 * @param class_id		// this is the class_id
	 * @param semester
	 * @param professor
	 * @param time
	 * @param location
	 * @return
	 */
	public boolean updateClassDetail(long class_id, String semester,
			String professor, String time, String location) {
		
		if (class_id != 0) {
			ContentValues updateValues = createClassDetailValues(class_id, semester, professor, time, location);
			
			return database.update(CLASS_DETAIL_TABLE, updateValues, CLASS_ID + "="
					+ class_id, null) > 0;
		}
		else {
			return false;
		}
	}

	/**
	 * Delete the class details
	 * @param rowId		// this is the class_id
	 * @return
	 */
	public boolean deleteClassDetail(long rowId) {
		return database.delete(CLASS_DETAIL_TABLE, CLASS_ID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Get a cursor positioned at a particular class detials
	 * @param rowId			// this is the class_id
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getClassDetail(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, CLASS_DETAIL_TABLE, new String[] {
				ROW_ID, CLASS_ID, CLASS_SEMESTER, CLASS_PROFESSOR, 
				CLASS_TIME, CLASS_LOCATION },
				CLASS_ID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}	
	
	
	/********************** Methods dealing with Events **********************/
	
	/**
	 * Create content values for the event table
	 * @param class_id
	 * @param event_type_id
	 * @param name
	 * @param due_date
	 * @param recurrence
	 * @return ContentValues
	 */
	private ContentValues createEventValues(long class_id, long event_type_id,
			String name, String due_date, long recurrence) {
		
		ContentValues values = new ContentValues();
		values.put(CLASS_ID, class_id);  // hard code 0 for manual events
		values.put(EVENT_TYPE_ID, event_type_id);
		values.put(EVENT_NAME, name);
		values.put(EVENT_DUE, due_date);
		values.put(EVENT_RECURRENCE, recurrence);		
		return values;
	}

	/**
	 * Create a new event
	 * If the event is successfully created then return the new
	 * rowId for that event, otherwise return a -1 to indicate failure.
	 * @param class_id
	 * @param event_type_id
	 * @param name
	 * @param due_date
	 * @param recurrence
	 * @return Integer
	 */
	public long insertEvent(long class_id, long event_type_id,
			String name, String due_date, long recurrence) {
		
		if (name.length() > 0 && due_date.length() > 0) {
			Cursor mCursor = getEvent(class_id, event_type_id, name, due_date);
			
			if (mCursor.getCount() > 0) {
				updateEvent(mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID)),
						class_id, event_type_id, name, due_date, recurrence);
				return mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID));
			}
			
			ContentValues eventValues = createEventValues(class_id, event_type_id,
					name, due_date,	recurrence);
	
			return database.insert(EVENT_TABLE, null, eventValues);
		}
		else {
			return -1;
		}
	}
	
	/**
	 * Update an event
	 * @param rowId
	 * @param class_id
	 * @param event_type_id
	 * @param name
	 * @param recurrence
	 * @param start_date
	 * @param due_date
	 * @return
	 */
	public boolean updateEvent(long rowId, long class_id, long event_type_id,
			String name, String due_date, long recurrence) {
		
		ContentValues updateValues = createEventValues(class_id, event_type_id,
				name, due_date,	recurrence);

		return database.update(EVENT_TABLE, updateValues, ROW_ID + "="
				+ rowId, null) > 0;
	}

	/**
	 * Delete an event
	 * @param rowId
	 * @return
	 */
	public boolean deleteEvent(long rowId) {
		// delete any associated reminder
		deleteEventReminder(rowId);
		
		// now delete the event 
		return database.delete(EVENT_TABLE, ROW_ID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Delete an event
	 * @param rowId
	 * @return
	 */
	public boolean deleteClassEvents(long classId) {
		// delete any associated reminder
		deleteClassReminders(classId);
		
		// now delete the event 
		return database.delete(EVENT_TABLE, CLASS_ID + "=" + classId, null) > 0;
	}

	/**
	 * Get all of the events from the events view
	 * includes the events, event type, event priority,
	 * and the event reminder
	 * @return Cursor
	 */
	public Cursor getViewAllEvents() {
		return database.query(EVENTS_VIEW, new String[] { EVENT_ID,
				CLASS_ID, EVENT_NAME, EVENT_DUE, EVENT_RECURRENCE,
				EVENT_TYPE_ID, EVENT_TYPE, EVENT_PRIORITY,
				PRIORITY_VAL, PRIORITY_COLOR, REMINDER_ID,
				REMINDER_MINUTES }, 
				EVENT_DUE + " > date('now','start of day')", 
				null, null, null, EVENT_DUE);		
	}
	
	/**
	 * Get all of the events from the events view
	 * includes the events, event type, event priority,
	 * and the event reminder
	 * @return Cursor
	 */
	public Cursor getViewAllMonthEvents(String iMonthDate) {
		return database.query(EVENTS_VIEW, new String[] { EVENT_ID,
				CLASS_ID, EVENT_NAME, EVENT_DUE, EVENT_RECURRENCE,
				EVENT_TYPE_ID, EVENT_TYPE, EVENT_PRIORITY,
				PRIORITY_VAL, PRIORITY_COLOR, REMINDER_ID,
				REMINDER_MINUTES }, 
				EVENT_DUE + " >= date('now','start of month') " + 
				"AND " + EVENT_DUE + " < date('now','start of month','+1 month')", 
				null, null, null, EVENT_DUE);		
	}
	
	/**
	 * Get all of the events for a class
	 * @return Cursor
	 */
	public Cursor getAllEvents() {
		return database.query(EVENT_TABLE, new String[] { ROW_ID,
				CLASS_ID, EVENT_TYPE_ID, EVENT_NAME, EVENT_DUE,
				EVENT_RECURRENCE }, 
				EVENT_DUE + " > date('now','start of day')", 
				null, null, null, EVENT_DUE);			
	}
	
	/**
	 * Get all of the events from the events view for a class
	 * includes the events, event type, event priority,
	 * and the event reminder
	 * @param rowId
	 * @return Cursor
	 */
	public Cursor getViewAllClassEvents(long rowId) {
		return database.query(EVENTS_VIEW, new String[] { EVENT_ID,
				CLASS_ID, EVENT_NAME, EVENT_DUE, EVENT_RECURRENCE,
				EVENT_TYPE_ID, EVENT_TYPE, EVENT_PRIORITY,
				PRIORITY_VAL, PRIORITY_COLOR, REMINDER_ID,
				REMINDER_MINUTES }, 
				CLASS_ID + "=" + rowId + " AND " +	EVENT_DUE + ">" + 
				"date('now','start of day')", null, null, null, EVENT_DUE);	
	}	
	
	/**
	 * Get all of the events for a class
	 * @param rowId
	 * @return Cursor
	 */
	public Cursor getAllClassEvents(long rowId) {
		return database.query(EVENT_TABLE, new String[] { ROW_ID,
				CLASS_ID, EVENT_TYPE_ID, EVENT_NAME, EVENT_DUE,
				EVENT_RECURRENCE }, 
				CLASS_ID + "=" + rowId + " AND " +	EVENT_DUE + ">" + 
				"date('now','start of day')", null, null, null, EVENT_DUE);			
	}
	
	/**
	 * Get a cursor positioned at a particular event
	 * from the view
	 * @param rowId
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getViewEvent(long rowId) throws SQLException {
		Cursor mCursor = database.query(EVENTS_VIEW, new String[] {
				EVENT_ID, CLASS_ID, EVENT_NAME, EVENT_DUE, EVENT_RECURRENCE,
				EVENT_TYPE_ID, EVENT_TYPE, EVENT_PRIORITY,
				PRIORITY_VAL, PRIORITY_COLOR, REMINDER_ID,
				REMINDER_MINUTES }, EVENT_ID + "=" + rowId, 
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}		
	
	/**
	 * Get a cursor positioned at a particular event
	 * @param rowId
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getEvent(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, EVENT_TABLE, new String[] {
				ROW_ID, CLASS_ID, EVENT_TYPE_ID, 
				EVENT_NAME,	EVENT_DUE, EVENT_RECURRENCE },
				ROW_ID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}		

	/**
	 * Get a cursor positioned at a particular event
	 * @param class_id
	 * @param event_type_id
	 * @param name
	 * @param due_date
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getEvent(long class_id, long event_type_id,
			String name, String due_date) throws SQLException {
		Cursor mCursor = database.query(true, EVENT_TABLE, new String[] {
				ROW_ID, CLASS_ID, EVENT_TYPE_ID, 
				EVENT_NAME,	EVENT_DUE, EVENT_RECURRENCE },
				CLASS_ID + "=? AND " + 
				EVENT_TYPE_ID + "=? AND " + 
				EVENT_NAME + "=? AND " +
				EVENT_DUE + "=?", new String[]{
				String.valueOf(class_id), 
				String.valueOf(event_type_id), name, due_date}, 
				null, null, null, null);
		
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	/********************** Methods dealing with Event Reminders **********************/
	
	/**
	 * Create content values for the event reminders table
	 * @param event_id
	 * @param minutes_prior
	 * @return ContentValues
	 */
	private ContentValues createReminderValues(long event_id, long minutes_prior) {
		
		ContentValues values = new ContentValues();
		values.put(REMINDER_EVENT_ID, event_id);
		values.put(REMINDER_MINUTES, minutes_prior);		
		return values;
	}

	/**
	 * Create a new event reminder
	 * If the event reminder is successfully created then return the new
	 * rowId for that event, otherwise return a -1 to indicate failure.
	 * @param event_id
	 * @param minutes_prior
	 * @return Integer
	 */
	public long insertReminder(long event_id, long minutes_prior) {
		
		if (Long.valueOf(minutes_prior) > 0) {
			Cursor mCursor = getReminder(event_id);
			
			if (mCursor.getCount() > 0) {
				updateReminder(mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID)),
						event_id, minutes_prior);
				return mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID));
			}
			
			ContentValues reminderValues = createReminderValues(event_id, minutes_prior);
	
			return database.insert(REMINDER_TABLE, null, reminderValues);
		}
		else {
			return -1;
		}
	}
	
	/**
	 * Update an event reminder
	 * @param rowId
	 * @param event_id
	 * @param minutes_prior
	 * @return Integer
	 */
	public boolean updateReminder(long rowId, long event_id, long minutes_prior) {
		
		ContentValues updateValues = createReminderValues(event_id, minutes_prior);

		return database.update(REMINDER_TABLE, updateValues, ROW_ID + "="
				+ rowId, null) > 0;
	}

	/**
	 * Delete a reminder
	 * @param rowId
	 * @return boolean
	 */
	public boolean deleteReminder(long rowId) {
		return database.delete(REMINDER_TABLE, ROW_ID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Delete the reminder for an event
	 * @param eventId
	 * @return boolean
	 */
	public boolean deleteEventReminder(long eventId) {
		return database.delete(REMINDER_TABLE, REMINDER_EVENT_ID + "=" + eventId, null) > 0;
	}
	
	/**
	 * Delete the reminders for a class 
	 * @param classId
	 */
	public void deleteClassReminders(long classId) {

		String sql = "DELETE FROM " + REMINDER_TABLE +
					" WHERE " + REMINDER_EVENT_ID + " IN (" + 
					"SELECT " + ROW_ID + " " +
					"FROM " + EVENT_TABLE + 
					" WHERE " + CLASS_ID + " = " + classId + ")";
		
		database.execSQL(sql);
	}

	/**
	 * Get a cursor positioned at a particular reminder
	 * @param rowId				// this is the event_id
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getReminder(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, REMINDER_TABLE, new String[] {
				ROW_ID, REMINDER_EVENT_ID, REMINDER_MINUTES },
				REMINDER_EVENT_ID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}		
	
	
	/********************** Methods dealing with Event Types **********************/
	
	/**
	 * Create content values for the event types table
	 * @param priority_id
	 * @param event_type
	 * @return ContentValues
	 */
	private ContentValues createEventTypeValues(long priority_id, String event_type) {
		
		ContentValues values = new ContentValues();
		values.put(EVENT_PRIORITY, priority_id);
		values.put(EVENT_TYPE, event_type);		
		return values;
	}

	/**
	 * Create a new event type
	 * If the event type is successfully created then return the new
	 * rowId for that event, otherwise return a -1 to indicate failure.
	 * @param priority_id
	 * @param event_type
	 * @return Integer
	 */
	public long insertEventType(long priority_id, String event_type) {
		
		if (event_type.length() > 0) {
			Cursor mCursor = getEventType(event_type);
			
			if (mCursor.getCount() > 0) {
				updateEventType(mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID)),
						priority_id, event_type);
				return mCursor.getInt(mCursor.getColumnIndex(CalDbAdapter.ROW_ID));
			}
			
			ContentValues eventTypeValues = createEventTypeValues(priority_id, event_type);
	
			return database.insert(EVENT_TYPE_TABLE, null, eventTypeValues);
		}
		else {
			return -1;
		}
	}
	
	/**
	 * Update an event type
	 * @param rowId
	 * @param priority_id
	 * @param event_type
	 * @return Integer
	 */
	public boolean updateEventType(long rowId, long priority_id, String event_type) {
		
		ContentValues updateValues = createEventTypeValues(priority_id, event_type);

		return database.update(EVENT_TYPE_TABLE, updateValues, ROW_ID + "="
				+ rowId, null) > 0;
	}

	/**
	 * Delete an event type
	 * @param rowId
	 * @return
	 */
	public boolean deleteEventType(long rowId) {
		return database.delete(EVENT_TYPE_TABLE, ROW_ID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Get all of the event types
	 * 		
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getAllEventType() throws SQLException {
		Cursor mCursor = database.query(EVENT_TYPE_TABLE, new String[] {
				ROW_ID, EVENT_PRIORITY, EVENT_TYPE },
				null, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}	

	/**
	 * Get a cursor positioned at a particular event type
	 * @param rowId				
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getEventType(long rowId) throws SQLException {
		Cursor mCursor = database.query(true, EVENT_TYPE_TABLE, new String[] {
				ROW_ID, EVENT_PRIORITY, EVENT_TYPE },
				ROW_ID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}	
	
	/**
	 * Get a cursor positioned at a particular event type
	 * @param event_type
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getEventType(String event_type) throws SQLException {
		Cursor mCursor = database.query(true, EVENT_TYPE_TABLE, new String[] {
				ROW_ID, EVENT_PRIORITY, EVENT_TYPE },
				EVENT_TYPE + "='" + event_type +"'", null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}	
	
	/********************** Methods dealing with Priority Levels *********************/
	
	/**
	 * Create content values for the priority levels table 
	 * @param priority_val
	 * @param color
	 * @return ContentValues
	 */
	private ContentValues createPriorityValues(long priority_val, String color) {
		
		ContentValues values = new ContentValues();
		values.put(PRIORITY_VAL, priority_val);
		values.put(PRIORITY_COLOR, color);
		return values;
	}
	
	/**
	 * Insert a new priority level into the database
	 * If the class is successfully created then return the new
	 * rowId for that class, otherwise return a -1 to indicate failure.
	 * @param priority_val
	 * @param color
	 * @return
	 */
	public long insertPriority(long priority_val, String color) {
		
		if (color.length() > 0) {
			ContentValues priorityValues = createPriorityValues(priority_val, color);
	
			return database.insert(PRIORITY_TABLE, null, priorityValues);
		}
		else {
			return -1;
		}
	}
	
	/**
	 * Update a priority level
	 * @param priority_val
	 * @param color
	 * @return
	 */
	public boolean updatePriority(long rowId, long priority_val, String color) {
		
		ContentValues updateValues = createPriorityValues(priority_val, color);
		
		return database.update(PRIORITY_TABLE, updateValues, ROW_ID + "="
				+ rowId, null) > 0;
	}

	/**
	 * Delete a priority level
	 * @param rowId
	 * @return
	 */
	public boolean deletePriority(long rowId) {
		return database.delete(PRIORITY_TABLE, ROW_ID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Get all of the priority levels
	 * @return Cursor
	 */
	public Cursor getAllPriorities() {
//		return database.query(PRIORITY_TABLE, new String[] { ROW_ID,
//				PRIORITY_VAL, PRIORITY_COLOR }, null, null, null, null, null);	
		
		String sql = "SELECT " + ROW_ID + ", " +
				"'Priority Level ' || " + PRIORITY_VAL + 
				" AS " + PRIORITY_VAL + ", " +
				PRIORITY_COLOR + " " +
				"FROM " + PRIORITY_TABLE;
		
		return database.rawQuery(sql, null);
	}
	
	/**
	 * Get a cursor positioned at a particular priority level
	 * @param rowId
	 * @return Cursor
	 * @throws SQLException
	 */
	public Cursor getPriority(long rowId) throws SQLException {		
		Cursor mCursor = database.query(true, PRIORITY_TABLE, new String[] {
				ROW_ID, PRIORITY_VAL, PRIORITY_COLOR }, ROW_ID + "=" + rowId, 
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}	
	
	/********************** Methods dealing with Elearning Log Data *********************/
	
	/**
	 * creates content values for the elearning log table
	 * @param logDate
	 * @param status
	 * @return
	 */
	private ContentValues createElearnLogValues(String logDate, String status) {
		
		ContentValues values = new ContentValues();
		values.put(ELEARN_LOG_DATE, logDate);
		values.put(ELEARN_LOG_STATUS, status);
		return values;
	}
	
	/**
	 * Deletes any existing log information and 
	 * inserts the new values
	 * @param logDate
	 * @param status
	 * @return
	 */
	public long insertElearnLog(String logDate, String status) {
		
		if (status.length() > 0) {
			// delete previous logs
			String sql = "DELETE FROM " + ELEARN_LOG_TABLE;
			
			database.execSQL(sql);
		
			// insert current log
			ContentValues elearnLogValues = createElearnLogValues(logDate, status);
	
			return database.insert(ELEARN_LOG_TABLE, null, elearnLogValues);
		}
		else {
			return -1;
		}
	}	
	
	public Cursor getElearnLog() {
		String sql = "SELECT " + ROW_ID + ", " +
				ELEARN_LOG_DATE + ", " +
				ELEARN_LOG_STATUS + " " +
				"FROM " + ELEARN_LOG_TABLE;
		
		return database.rawQuery(sql, null);
	}
	
		
	/**
	 * Adds some demo data to the database
	 */
	public void addDummy() {		
		// insert some classes
		insertClass("CS", "4347", ".002", "Database Systems");
		insertClass("SE", "4352", ".501", "Software Design & Architecture");
		insertClass("AIM", "3320", ".004", "Intermediate Accounting");		
		
		// insert the class details
		insertClassDetail(1, "F11", "Joe Smoe", "6 pm", "ECSS 3.221");
		insertClassDetail(2, "F11", "Tom Smith", "9 am", "ECSN 3.211");
		insertClassDetail(3, "S12", "Steve Barnes", "10 am", "ECSS 2.231");
		
		//ex.  dbHelper.insertEvent(class_id, event_type_id, name, due_date, recurrence);
		insertEvent(1, 2, "Final", "2011-12-14 16:00", 0);
		insertEvent(2, 4, "Home Work #4", "2011-12-15 12:00", 0);
		insertEvent(3, 4, "Home Work #2", "2011-12-15 00:00", 0);
		
		// insert a reminder for the mid term 
		//dbHelper.insertReminder(1, 24*60);
		insertElearnLog("2011-11-16 11:53", "Success");
		insertElearnLog("2011-11-16 11:58", "Success");
		insertElearnLog("2011-11-17 00:31", "Success");
	}	
}